﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Common.DB
{
    public class SqlHelper
    {
        #region "私有变量"
        protected System.Data.SqlClient.SqlConnection Con = new SqlConnection();			//数据库打开连接
        protected System.Data.SqlClient.SqlCommand Cmd = new SqlCommand();				    //表示执行对象是SQl还是存储过程
        protected System.Data.SqlClient.SqlDataAdapter Dtapt = new SqlDataAdapter();		//数据设配器
        protected System.Data.SqlClient.SqlTransaction sqlTran;                             //事务
        protected SqlDataReader dtrValue = null;                                            //读取数据
        #endregion

        #region"数据库连接处理"

        public int iConnectString = 1;

        /// <summary>
        /// 获得webconfig中的 默认 Sql连接字符串
        /// </summary>
        public string strConSql
        {
            get
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["BSConn"].ToString();
            }

        }
        #endregion

        #region "事务处理"


        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            if (Con.State == ConnectionState.Closed)
            {
                //打开连接
                OpenCn();
                //开始事务
                if (sqlTran == null)
                {
                    sqlTran = Con.BeginTransaction();
                }
                Cmd.Transaction = sqlTran;
            }
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransection()
        {
            sqlTran.Commit();
            sqlTran.Dispose();
            sqlTran = null;
            CloseCn();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransection()
        {
            sqlTran.Rollback();
            sqlTran.Dispose();
            sqlTran = null;
            CloseCn();
        }

        #endregion

        #region "初始化数据连接"
        /// <summary>
        /// 初始化数据连接
        /// </summary>
        /// <returns>数据连接SqlConnection</returns>
        protected System.Data.SqlClient.SqlConnection GetConnection()
        {
            string strConnectString;
            strConnectString = strConSql;
            SqlConnection Con1 = new SqlConnection(strConnectString);
            return Con1;
        }

        #endregion

        #region "数据库的关闭连接"
        /// <summary>
        /// 打开数据连接
        /// </summary>
        protected void OpenCn()
        {
            //判断事务是否开始
            if (sqlTran == null)
            {
                Con = GetConnection();
            }
            if (Con.State != ConnectionState.Open)
            {
                Con.Open();
            }
        }

        /// <summary>
        /// 关闭数据连接
        /// </summary>
        protected void CloseCn()
        {
            if (Con.State != ConnectionState.Closed && sqlTran == null)
            {
                Con.Close();
                Con.Dispose();
                Cmd.Dispose();
            }
        }

        #endregion

        #region "ExecuteReader 执行SQL语句"
        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="objCmdType">命令类型 1 SQL语句 2存储过程</param>
        /// <param name="strSQL">命令类型 1 SQL语句 2存储过程</param>
        /// <param name="p_arrparmCmdParms">SqlParameter</param>
        /// <returns>SqlDataReader</returns>
        protected SqlDataReader ExecuteReader(CommandType objCmdType, string p_strSQL, SqlParameter[] p_arrparmCmdParms)
        {
            SqlDataReader dtrRet = null;
            try
            {
                //打开连接
                OpenCn();
                //命令行连接
                Cmd.Connection = Con;
                Cmd.CommandText = p_strSQL;
                Cmd.CommandTimeout = 120;
                //是SQL语句还是存储过程
                Cmd.CommandType = objCmdType;

                //循环arrparmCmdParms值
                if (p_arrparmCmdParms != null)
                {
                    foreach (SqlParameter objParm in p_arrparmCmdParms)
                        Cmd.Parameters.Add(objParm);
                }
                dtrRet = Cmd.ExecuteReader();
                Cmd.Parameters.Clear();
                return dtrRet;
            }
            catch (Exception)
            {
                if (Con.State != ConnectionState.Closed && sqlTran == null)
                {
                    Cmd.Parameters.Clear();
                    CloseCn();
                }
                // 写错误日志
                //eHR.CommModules.Function.AddLog.AddNewXmlLog(//eHR.CommModules.Function.LogType.Error, "错误 : " + e.Message + "\n\r SQL : " + p_strSQL + "\n\r 参数 : " + string.Empty, e.StackTrace);
                return null;
            }
            finally
            {
                //如果连接打开并且没有事务和SqlDataReader事件 则关闭连接
                if (Con.State != ConnectionState.Closed && sqlTran == null && dtrRet == null)
                {
                    Cmd.Parameters.Clear();
                    CloseCn();
                }
            }
        }
        #endregion

        #region "ExecuteNonQuery 执行SQL语句或者存储过程,返回所影响行数"

        /// <summary>
        /// 执行不带不带参数的SQL
        /// </summary>
        /// <param name="p_strSQL">sql</param>
        /// <returns>返回数据集合</returns>
        protected int ExecuteSQL(string p_strSQL)
        {
            SqlParameter[] objarrparmCmdParms = new SqlParameter[0];
            return ExecuteSQL(p_strSQL, objarrparmCmdParms);
        }

        /// <summary>
        /// 执行带参数的SQL
        /// </summary>
        /// <param name="p_strSQL">带参数的SQL语句</param>
        /// <param name="p_arrparmCmdParms">参数名称对应的值</param>
        /// <returns>影响的行数</returns>
        protected int ExecuteSQL(string p_strSQL, SqlParameter[] p_arrparmCmdParms)
        {
            return ExecuteNonQuery(CommandType.Text, p_strSQL, p_arrparmCmdParms);
        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="p_strSPName">存储过程名称</param>
        /// <param name="p_arrparmCmdParms">参数名称对应的值</param>
        /// <returns>影响的行数</returns>
        protected int ExecuteSP(string p_strSPName, SqlParameter[] p_arrparmCmdParms)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, p_strSPName, p_arrparmCmdParms);
        }
        /// <summary>
        /// 执行SQL语句或者存储过程 [-1==>执行语句出错 在外面有事务一定要回滚!!!!!!!]
        /// </summary>
        /// <param name="objCmdType">命令类型 1 SQL语句 2存储过程</param>
        /// <param name="p_strSQL">SQL名称或者是存储过程名称</param>
        /// <param name="p_arrparmCmdParms">SqlParameter参数列表</param>
        /// <returns>执行语句后影响的行数</returns>
        protected int ExecuteNonQuery(CommandType objCmdType, string p_strSQL, SqlParameter[] p_arrparmCmdParms)
        {
            try
            {
                OpenCn();
                Cmd.Connection = Con;
                Cmd.CommandText = p_strSQL;
                Cmd.CommandType = objCmdType;

                if (p_arrparmCmdParms != null)
                {
                    foreach (SqlParameter objParm in p_arrparmCmdParms)
                        Cmd.Parameters.Add(objParm);
                }
                int intRet = Cmd.ExecuteNonQuery();
                Cmd.Parameters.Clear();
                return intRet;
            }
            catch (Exception)
            {
                ////eHR.CommModules.Function.AddLog.WriteLog(e);
                string strErr = "";
                //记录出错参数列表
                if (Cmd.Parameters.Count > 0)
                {
                    for (int i = 0; i < Cmd.Parameters.Count; i++)
                    {
                        strErr = strErr + Cmd.Parameters[i].ParameterName + "='" + Cmd.Parameters[i].Value + "'  ";
                    }
                }

                if (Con.State != ConnectionState.Closed && sqlTran == null)
                {
                    Cmd.Parameters.Clear();
                    CloseCn();
                }
                return -1;
            }

            finally
            {
                //如果有事物则不关闭连接
                if (Con.State != ConnectionState.Closed && sqlTran == null)
                {
                    Cmd.Parameters.Clear();
                    CloseCn();
                }
            }
        }
        #endregion

        #region "返回表数据Datatable  [DataSet]方式获取数据  注意！一般情况下不使用"

        protected DataTable ExecuteDataSetTable(string p_strSQL)
        {
            SqlParameter[] p_arrparmCmdParms = new SqlParameter[0];
            return ExecuteDataSetTable(p_strSQL, p_arrparmCmdParms);
        }

        /// <summary>
        /// 返回表数据   未测试
        /// </summary> 
        /// <param name="objCmdType">命令类型 1 SQL语句 2存储过程</param>
        /// <param name="p_strSQL">可是是SQL 也可以是存储过程</param>
        /// <param name="p_arrparmCmdParms">SqlParameter参数列表</param>
        /// <returns>DataTable</returns>
        protected DataTable ExecuteDataSetTable(string p_strSQL, SqlParameter[] p_arrparmCmdParms)
        {
            DataTable dtb = new DataTable();
            try
            {
                OpenCn();
                Cmd.Connection = Con;
                Cmd.CommandText = p_strSQL;
                Cmd.CommandType = CommandType.Text;

                if (p_arrparmCmdParms != null)
                {
                    Cmd.Parameters.Clear();
                    foreach (SqlParameter objParm in p_arrparmCmdParms)
                        Cmd.Parameters.Add(objParm);
                }
                SqlDataAdapter Dtapt = new SqlDataAdapter(Cmd);
                DataSet dst = new DataSet();
                Dtapt.Fill(dst);
                Cmd.Parameters.Clear();
                dtb = dst.Tables[0].Copy();
                dst.Clear();
                dst.Dispose();
                return dtb;
            }
            catch (Exception)
            {
                if (Con.State != ConnectionState.Closed && sqlTran == null)
                {
                    Cmd.Parameters.Clear();
                    CloseCn();
                }
                return dtb;
            }

            finally
            {
                if (Con.State != ConnectionState.Closed && sqlTran == null)
                {
                    Cmd.Parameters.Clear();
                    CloseCn();
                }
            }
        }

        #endregion

        #region "返回表数据Datatable  [Read]方式获取数据"

        /// <summary>
        /// 执行不带参数的SQL语句
        /// </summary>
        /// <param name="p_strSql">SQL</param>
        /// <returns>返回数据集合</returns>
        protected DataTable ExecuteReadTable(string p_strSql)
        {
            SqlParameter[] objarrparmCmdParms = new SqlParameter[0];
            return ExecuteReadTable(p_strSql, objarrparmCmdParms);
        }

        /// <summary>
        /// 执行不带参数的SQL语句
        /// </summary>
        /// <param name="p_strSql">SQL</param>
        /// <param name="p_intStart">起始位置</param>
        /// <param name="p_intPageSize">结束位置</param>
        /// <param name="out_intCount">当前页数</param>
        /// <returns>返回数据集合</returns>
        protected DataTable ExecuteReadTable(string p_strSql, int p_intStart, int p_intPageSize, ref  int out_intCount)
        {
            SqlParameter[] objarrparmCmdParms = new SqlParameter[0];
            return ExecuteReadTable(CommandType.Text, p_strSql, objarrparmCmdParms, p_intStart, p_intPageSize, ref out_intCount);
        }

        /// <summary>
        /// 1. 根据存储过程和参数值得到DataTable 值
        /// 2. 根据SQL的得到DataTable 值
        /// </summary>
        /// <param name="p_intStart">开始记录</param>
        /// <param name="p_intPageSize">每页显示条数</param>
        /// <param name="p_strSql">可是是SQL 也可以是存储过程</param>
        /// <param name="p_aryParm">SqlParameter参数列表</param>
        /// <param name="out_intCount">返回总记录数</param>
        /// <returns>返回DataTable</returns>
        protected DataTable ExecuteReadTable(CommandType objCmdType, string p_strSql, SqlParameter[] p_arrparmCmdParms, int p_intStart, int p_intPageSize, ref  int out_intCount)
        {
            DataTable dtb = new DataTable();
            DateTime dtStart = DateTime.Now;

            dtrValue = ExecuteReader(objCmdType, p_strSql, p_arrparmCmdParms);
            if (dtrValue == null)
            {
                CloseCn();
                return dtb;
            }
            int intColLength = dtrValue.FieldCount;

            for (int i = 0; i < intColLength; i++)
            {
                //构造sql的table
                dtb.Columns.Add(dtrValue.GetName(i), GetColType(i));
            }
            DataRow dr;

            int k = 0;
            if (dtrValue.HasRows)
            {
                //读取数据行值
                while (dtrValue.Read())
                {
                    //读取分页间数据
                    if (p_intStart <= k && k < p_intStart + p_intPageSize)
                    {
                        dr = dtb.NewRow();
                        //读取每列值
                        for (int j = 0; j < intColLength; j++)
                        {
                            //读取每列的值
                            dr[dtrValue.GetName(j)] = GetValue(j, GetFieldType(j).ToString());
                        }
                        dtb.Rows.Add(dr);
                    }
                    k++;
                }
            }
            CloseCn();
            if (out_intCount == 0)
            {
                out_intCount = k;//获得总行数并且返回到页面
            }
            return dtb;
        }

        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="p_strSql">SQL</param>
        /// <param name="p_arrparmCmdParms">Parameter参数所对应的值</param>
        /// <returns>返回数据集合</returns>
        protected DataTable ExecuteReadTable(string p_strSql, SqlParameter[] p_arrparmCmdParms)
        {
            return ExecuteReadTable(CommandType.Text, p_strSql, p_arrparmCmdParms);
        }
        /// <summary>
        /// 1. 根据存储过程和参数值得到DataTable 值
        /// 2. 根据SQL的得到DataTable 值
        /// 20000次的循环读取话费时间在2.5秒左右(注意大数据量读取性能问题)
        /// </summary>
        /// <param name="p_strSql">可是是SQL 也可以是存储过程</param>
        /// <param name="p_aryParm">SqlParameter参数列表</param>
        /// <returns>返回DataTable</returns>

        protected DataTable ExecuteReadTable(CommandType objCmdType, string p_strSql, SqlParameter[] p_arrparmCmdParms)
        {
            DataTable dtb = new DataTable();
            //执行sql获取Read
            dtrValue = ExecuteReader(objCmdType, p_strSql, p_arrparmCmdParms);
            if (dtrValue == null)
            {
                CloseCn();
                return dtb;
            }
            int intColLength = dtrValue.FieldCount;
            for (int i = 0; i < intColLength; i++)
            {
                //构造sql的table
                dtb.Columns.Add(dtrValue.GetName(i), GetColType(i));
            }
            DataRow dr;
            if (dtrValue.HasRows)
            {
                //读取数据行值
                while (dtrValue.Read())
                {
                    dr = dtb.NewRow();
                    //读取每列值
                    for (int j = 0; j < intColLength; j++)
                    {
                        //读取每列的值
                        dr[dtrValue.GetName(j)] = GetValue(j, GetFieldType(j).ToString());
                    }
                    dtb.Rows.Add(dr);
                }
            }
            CloseCn();
            dtrValue.Close();
            return dtb;
        }



        /// <summary>
        /// SQLRead根据类型得到该列数据的数据类型
        /// </summary>
        /// <param name="p_intIndex">select 出来的table的列的索引</param>
        /// <returns>该列数据的数据类型</returns>
        protected System.Type GetColType(int p_intIndex)
        {
            switch (dtrValue.GetFieldType(p_intIndex).ToString())
            {
                case "System.Guid":
                    return typeof(string);
                case "System.String":
                    return typeof(string);
                case "System.Double":
                    return typeof(Double);
                case "System.Int":
                    return typeof(int);
                case "System.DateTime":
                    return typeof(DateTime);
                case "System.Byte[]":
                    return typeof(byte[]);
                case "System.Decimal":
                    return typeof(Decimal);
                default:
                    return typeof(string);
            }
        }

        /// <summary>
        /// SQLRead根据类型得到该列数据的读取得方式
        /// </summary>
        /// <param name="p_intIndex">select 出来的table的列的索引</param>
        /// <param name="p_strType">table的索引列的数据类型</param>
        /// <returns>该索引类的值</returns>
        protected Object GetValue(int p_intIndex, string p_strType)
        {
            switch (p_strType)
            {
                case "System.Guid":
                    return GetGuid(p_intIndex);
                case "System.String":
                    return GetString(p_intIndex);
                case "System.Byte[]":
                    return GetBytes(p_intIndex, 7000);
                case "System.DateTime":
                    return GetDateTime(p_intIndex);
                case "System.Double":
                    return GetDouble(p_intIndex);
                case "System.Int16":
                    return GetInt16(p_intIndex);
                case "System.Int32":
                    return GetInt32(p_intIndex);
                case "System.Decimal":
                    return GetDecimal(p_intIndex);
                default:
                    return GetString(p_intIndex);
            }

        }

        /// <summary>
        /// 获取Read项的列的类型值
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected System.Type GetFieldType(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                return dtrValue.GetFieldType(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }
        #endregion

        # region  "SQLRead 读取数据  可能会缺少 "

        /// <summary>
        /// String
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        public string GetString(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return null;
                else return dtrValue.GetString(intIndex).ToString();
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        /// <summary>
        /// 用于10进制的数据读取
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected Decimal GetDecimal(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return Decimal.MinValue;
                else return dtrValue.GetDecimal(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        /// <summary>
        /// 读取Int32
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected Int32 GetInt32(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return int.MinValue;
                else return dtrValue.GetInt32(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        protected Int32 GetInt(int intIndex)
        {
            return GetInt32(intIndex);
        }

        protected Int32 GetInt16(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return Int32.MinValue;
                else return (int)dtrValue.GetInt16(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        protected Int32 GetSmallInt(int intIndex)
        {
            return GetInt16(intIndex);
        }

        /// <summary>
        /// float
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected float GetReal(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return Single.MinValue;
                else return dtrValue.GetFloat(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        protected float GetFloat(int intIndex)
        {
            return (float)GetDouble(intIndex);
        }

        /// <summary>
        /// double
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected double GetDouble(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return double.MinValue;
                else return dtrValue.GetDouble(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        /// <summary>
        /// GetGuid
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected string GetGuid(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return string.Empty;
                else return dtrValue.GetGuid(intIndex).ToString();
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        /// <summary>
        /// DateTime
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected DateTime GetDateTime(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return DateTime.MinValue;
                else return dtrValue.GetDateTime(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        /// <summary>
        /// char
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected char GetChar(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return Char.MinValue;
                else
                {
                    string strRet = dtrValue.GetString(intIndex).Trim();
                    if (strRet == String.Empty)
                        return Char.MinValue;
                    else
                        return strRet[0];
                }
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");

        }

        /// <summary>
        /// 用于image的读取
        /// </summary>
        /// <param name="intIndex"></param>
        /// <param name="intLength"></param>
        /// <returns></returns>
        protected byte[] GetBytes(int intIndex, int intLength)
        {

            byte[] arrbyt1 = new Byte[0];
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true)
                {

                    return arrbyt1;
                }
                else
                {
                    byte[] arrbyt = new byte[intLength];
                    dtrValue.GetBytes(intIndex, 2, arrbyt, 0, intLength);
                    return arrbyt;
                }
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        protected byte GetByte(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return Byte.MaxValue;
                else return dtrValue.GetByte(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        /// <summary>
        /// 将bool转换成 0 或1
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected byte GetBool2Byte(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return Byte.MaxValue;
                else
                {
                    if (dtrValue.GetBoolean(intIndex)) return 1;
                    else return 0;
                }
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        /// <summary>
        /// 返回bool值
        /// </summary>
        /// <param name="intIndex"></param>
        /// <returns></returns>
        protected bool GetBoolean(int intIndex)
        {
            if (dtrValue != null && dtrValue.IsClosed == false)
            {
                if (dtrValue.IsDBNull(intIndex) == true) return false;
                else return dtrValue.GetBoolean(intIndex);
            }
            else throw new InvalidOperationException("SqlDBBase中dtrValue为null");
        }

        #endregion
    }
}
